﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using DTO;
namespace DAO
{
    public class DaiLyDAO
    {
        public static void InsertDaiLy(DaiLyDTO daiLy)
        {
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction trans = conn.BeginTransaction();
            try
            {
                String strSql = "insert into DaiLy(TenDaiLy, DiaChi, DienThoai, Quan) values(?, ?, ?, ?)";                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = trans;
                com.Parameters.Add("@TenDaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@DiaChi", OleDbType.VarWChar);
                com.Parameters.Add("@DienThoai", OleDbType.VarWChar);
                com.Parameters.Add("@Quan", OleDbType.VarWChar);

                com.Parameters["@TenDaiLy"].Value = daiLy.TenDaiLy;
                com.Parameters["@DiaChi"].Value = daiLy.DiaChi;
                com.Parameters["@DienThoai"].Value = daiLy.DienThoai;
                com.Parameters["@Quan"].Value = daiLy.Quan;
                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                com.Transaction = trans;
                daiLy.Id = int.Parse(com.ExecuteScalar().ToString());
                trans.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                conn.Close();
                throw;
            }
        }
        public static void UpdateDaiLy(DaiLyDTO daiLy)
        {
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction trans = conn.BeginTransaction();
            try
            {
                String strSql = "update DaiLy set TenDaiLy=?, Quan=? where ID=?";
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = trans;
                com.Parameters.Add("@TenDaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@Quan", OleDbType.VarWChar);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@TenDaiLy"].Value = daiLy.TenDaiLy;
                com.Parameters["@Quan"].Value = daiLy.Quan;
                com.Parameters["@ID"].Value = daiLy.Id;
                com.ExecuteNonQuery();
                trans.Commit();                
                conn.Close();
            }
            catch (Exception ex) {
                trans.Rollback();
                conn.Close();
                throw;
            }
        }
        public static void DeleteDaiLy(int id)
        {
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction trans = conn.BeginTransaction();
            try
            {
                String strSql = "delete from DaiLy where ID=?";
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = trans;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
                trans.Commit();
                conn.Close();
            }
            catch (Exception ex) {
                trans.Rollback();
                conn.Close();
                throw;
            }
        }
    }
}
